package com.film.service.impl.movie;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.film.service.movie.FetchMoviesService;
import com.film.utils.MySqlConnection;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

@Service
public class FetchMoviesServiceImpl implements FetchMoviesService {
    @Override
    public JSONObject fetchMovies() throws SQLException {
        JSONObject resp = new JSONObject();
        HashMap<Integer, JSONObject> items = new HashMap<>();
        String commentSql = "select Movie.id, comment, username, photo_url from Movie, Movie_Comments, User where Movie.id=Movie_Comments.movie_id and Movie_Comments.commentator_id = User.id;";
        String movieSql = "select * from Movie";
        Statement stmt = MySqlConnection.connect.createStatement();
        ResultSet resultSet = stmt.executeQuery(movieSql);
        while (resultSet.next()) {
            JSONObject item = new JSONObject();
            Integer movieId = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String imgUrl = resultSet.getString("image");
            Integer stars = resultSet.getInt("stars");
            item.put("movieId", movieId);
            item.put("name", name);
            item.put("imgUrl", imgUrl);
            item.put("stars", String.valueOf(stars));
            item.put("comments", new ArrayList<JSONObject>());
            items.put(movieId, item);
        }

        ResultSet commentsResult = stmt.executeQuery(commentSql);
        while (commentsResult.next()) {
            JSONObject comments = new JSONObject();
            int movieId = commentsResult.getInt("Movie.id");
            String comment = commentsResult.getString("comment");
            String username = commentsResult.getString("username");
            String avatarUrl = commentsResult.getString("photo_url");
            comments.put("comment", comment);
            comments.put("name", username);
            comments.put("avatarUrl", avatarUrl);
            JSONObject item = items.get(movieId);
            ArrayList<JSONObject> commentsArray = (ArrayList<JSONObject>) item.get("comments");
            commentsArray.add(comments);
        }

        resp.put("movies", items);
        resp.put("movies_count", items.size());
        return resp;

    }
}
